package analyze;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Room4 {
	static Connection conn=null;	//	连接对象
	static PreparedStatement stmt=null;	//	发送sql语句
	static ResultSet rs=null;	//	sql返回的结果集
	static Student student=new Student();	//	student对象
	static ArrayList<String> arr=new ArrayList<String>();//4人寝
	public static void main(String[] args) {
		
	}
	private static ArrayList<String> selectClassName(){
		String sql="select distinct className where roomSize='4' from student";//查询student数据
		ArrayList<String> a=new ArrayList<String>();
		conn=Conn.getConn();//获得数据库连接
		try {
			stmt=conn.prepareStatement(sql);//	执行sql语句
			rs=stmt.executeQuery();//	班级的种类
			while(rs.next()){
				a.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//			关闭数据库连接
			Conn.close(conn, stmt, rs);
		}
		return a;
	}

	//"melancholic",
	public static ArrayList<String> findMelancholicName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%elancholi%'";
		conn=Conn.getConn();
		ArrayList<String> arrM=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				//为bean对象赋值
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				//				标记为已经分配
//				setSign(rs.getString(1));
				arrM.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrM;
	}
	//	寝室的choleric人员的寻找返回 name
	public static ArrayList<String> findCholericName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%holeri%'";
		conn=Conn.getConn();
		ArrayList<String> arrC=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				arrC.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrC;
	}
	//	寝室的sanguine人员的寻找返回 name
	public static ArrayList<String> findSanguineName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%anguin%'";
		conn=Conn.getConn();
		ArrayList<String> arrS=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				arrS.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrS;
	}
	//	寝室的phlegmatic人员的寻找返回 name
	public static ArrayList<String> findPhlegmaticName(String test){
		//		寝室的核心人员数量sql查询语句 核心人员为领导型
		String sql="select id,className,name from student where test=? and sign='0' and temperament like '%hlegmati%'";
		conn=Conn.getConn();
		ArrayList<String> arrP=new ArrayList<String>();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, test);
			rs=stmt.executeQuery();
			while(rs.next()){
				student.setId(rs.getString(1));
				student.setClassName(rs.getString(2));
				student.setName(rs.getString(3));
				arrP.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt, rs);
		}
		//		失败返回空	
		return arrP;
	}
	public static void setSign(String id){
		String sql="update student set sign='1' where id=?";
		conn=Conn.getConn();
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Conn.close(conn, stmt);
		}
	}
}
